/+
Library dbapi - Simple database api for the D programming language.

Copyright (C) 2007 Myron Alexander (myron.alexander...gmail.com)

This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+/

module dbapi.internal.sqlite3.statement;

private import std.boxer;
private import std.string : strip, iswhite, toupper, toStringz;
private import std.outofmemory : _d_OutOfMemory;
private import std.c.string;

private import dbapi.base;

private import dbapi.internal.sqlite3.internalconnection;
private import dbapi.internal.sqlite3.error;
private import dbapi.internal.sqlite3.definitions;

private import sqlite.sqlite3;



/**
 * Creates a statement.
 *
 * Only single query statements are supported.
 */
public class Statement {

   private InternalConnection m_iconn;
   private sqlite3_stmt*      m_stmt;
   private string             m_text;
   private int                m_compiledColumnCount;
   private int                m_resultsetColumnCount;
   private StatementType      m_statementType;
   private int                m_numBindParameters;

   /**
    * For UnitTests.
    */
   private this () {
   }

   /**
    * Construct the statement from the SQL in text using the connection details
    * in iconn.
    *
    * Params:
    *    iconn = Connection to construct the statement. The connection must be
    *            open.
    *
    *    text  = SQL statement to construct. The text must be populated with a
    *            valid SQL statement for the connection.
    */
   this (InternalConnection iconn, string text)
   in {
      assert (iconn !is null);
      assert (iconn.isOpen (),
         "Cannot create statement of a closed connection.");
      assert (text !is null);
      assert (text.length > 0);
   }
   out {
      assert (m_iconn is iconn);
      assert (m_text is text);
      assert (m_stmt !is null);
   }
   body {
      m_iconn = iconn;
      m_text  = text;

      /* Determine the statement type.
       */
      m_statementType = determineStatementType (text);

      try {
         m_iconn.transModeHandler.validateStatementType (m_statementType);
      } catch (SqlInterfaceException e) {
         e.setSql (text);
         throw e;
      }

      char *pzTail;

      int rc =
         sqlite3_prepare_v2 (
            m_iconn.dbcon, toStringz (text), text.length, &m_stmt, &pzTail);

      switch (rc) {
         case SQLITE_OK:
            break;

         case SQLITE_ERROR:
            throw (SqliteError!(SqlProgrammingException) (
               m_iconn.dbcon,
               "Unable to create statement"))
            .setSql (text)
            .setDsn (m_iconn.dsn);

         default:
            throw (SqliteError!(SqlOperationalException) (
               m_iconn.dbcon,
               "Unable to create statement"))
            .setSql (text)
            .setDsn (m_iconn.dsn);
      }

      // Test whether the input operation text contains a statement separator.
      // Since multiple operations per statement are not allowed, the presence
      // of the separator is illegal. The variable pzTail will only point to a
      // remainder if the statement separator is parsed.

      if (pzTail !is null && strlen (pzTail) > 0) {
         throw (new SqlInterfaceException (
            "Invalid SQL, multiple statements not supported"))
            .setLongMsg (
               "Illegal SQL text passed to create statement instance. "
               "Statements only support single operations but the supplied "
               "SQL text contains the operation separator ';' and is thus "
               "invalid.")
         .setSql (text)
         .setDsn (m_iconn.dsn);
      }

      /* Calculate the compiled column count for the statement and cache the
       * result.
       */
      m_compiledColumnCount = sqlite3_column_count (m_stmt);

      /* Request the number of bind parameters.
       */
      m_numBindParameters = sqlite3_bind_parameter_count (m_stmt);
   }

   ~this () {
      close ();
   }

   /**
    * Execute the statement.
    *
    * Returns:
    *    true when a resultset is available, else false.
    */
   bool step ()
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");

      assert (m_stmt !is null,
         "Unable to execute statement as Statement instance is closed.");
   }
   body {
      int rc = sqlite3_step (m_stmt);

      m_resultsetColumnCount = sqlite3_data_count (m_stmt);

      switch (rc) {
         case SQLITE_DONE:
            return false;

         case SQLITE_ROW:
            return true;

         case SQLITE_CONSTRAINT:
            //TODO check if still in transaction
            throw SqliteError!(SqlIntegrityException) (
               m_iconn.dbcon,
               "Error on executing operation step. "
               "The operation caused a constraint violation.")
               .setSql (m_text);

         case SQLITE_SCHEMA:
            throw SqliteError!(SqlInternalException) (
               m_iconn.dbcon,
               "Error on executing operation step. "
               "The step could not be performed as the database schema has "
               "changed. ")
               .setSql (m_text);

         case SQLITE_BUSY:
            throw SqliteError!(SqlOperationalException) (
               m_iconn.dbcon,
               "Error on executing operation step. "
               "Unable to execute as the database is busy. This should only "
               "be possible in multi-threaded environment. This api does "
               "not support multi-threaded operation.")
               .setSql (m_text);

         default:
            throw SqliteError!(SqlOperationalException) (
               m_iconn.dbcon,
               "Error on executing operation step. "
               "Sqlite error ($(errmsg)).")
               .setSql (m_text);
      }
   }

	void reset ()
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");

      assert (m_stmt !is null,
         "Unable to reset statement as Statement instance is closed.");
   }
   body {
      int rc = sqlite3_reset (m_stmt);

      if (rc != SQLITE_OK) {
         throw SqliteError!(SqlOperationalException) (
            m_iconn.dbcon,
            "Error on statement reset for statement. "
            "Sqlite error ($(errmsg)).")
            .setSql (m_text);
      }

	}



   void close () {
      if (m_stmt !is null) {
         if (m_iconn.isOpen ()) {
            int rc = sqlite3_finalize (m_stmt);

            switch (rc) {
               case SQLITE_OK:
                  break;

               case SQLITE_ABORT:
                  // Closing is done after the block?
                  // TODO: Should I allow finalize on a busy operation?
                  //       Is this even possible in the current implementation?
                  //       For single threads, this should not be possible and
                  //       dbapi does not support multi-threaded operation.
                  m_iconn.transModeHandler.onAbort ();
                  break;

               default:
                  throw SqliteError!(SqlOperationalException) (
                     m_iconn.dbcon,
                     "Error on closing statement")
                     .setSql (m_text);
            }
         }
      }

      // Reset instance values to closed state.
      m_iconn                = null;
      m_stmt                 = null;
      m_text                 = "";
      m_compiledColumnCount  = typeof (m_compiledColumnCount).init;
      m_resultsetColumnCount = typeof (m_resultsetColumnCount).init;
   }


   bool isOpen () {
      return m_iconn.isOpen () && m_stmt !is null;
   }


   bool isClosed () {
      return !m_iconn.isOpen () || m_stmt is null;
   }


   sqlite3_stmt* stmt ()
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");

      assert (m_stmt !is null,
         "Unable to retrieve statement as Statement instance is closed.");
   }
   body {
      return m_stmt;
   }


   /**
    * Property returns the SQL text that this statement was created with. When
    * the statement is closed, the text is set to an empty string.
    */
   string text ()
   in {
      assert (m_text !is null);
   }
   body {
      return m_text;
   }


   StatementType statementType () {
      return m_statementType;
   }


   /**
    * Returns the number of bind parameters.
    *
    * Will return 0 when the statement has no bind parameters otherwise a
    * positive number is returned.
    */
   int numBindParameters () {
      return m_numBindParameters;
   }


   /**
    * Returns the number of records touched by the last statement executed.
    */
   int numChanges ()
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");

      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
   }
   body {
      if (StatementType.STATEMENT_MODIFY == m_statementType)
         return sqlite3_changes (m_iconn.dbcon);
      else
         return 0;
   }


   //---------------------------------------------------------------------------
   // Section: Variable binding
   //
   // The following allow for binding values to a statement.
   //---------------------------------------------------------------------------

   void clearBindings ()
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
   }
   body {
      int rc = sqlite3_clear_bindings (m_stmt);

      if (SQLITE_OK != rc) {
         throw SqliteError!(SqlOperationalException) (
            m_iconn.dbcon,
            "Failed to clear bindings.")
            .setSql (m_text);
      }
   }

   int parmIndexFromName (string name)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
   }
   body {
      /* We have to prepend the ":" as sqlite stores the parameter name as-is
       * including the ":".
       */
      int position = sqlite3_bind_parameter_index (m_stmt, toStringz (":"~name));

      // When the bind name does not exist, throw an error.
      if (0 == position) {
         throw (new SqlProgrammingException (
            "Parameter name not found. The SQL statement does not define a "
            "parameter name (" ~ name ~ ")."))
            .setSql (m_text);

      } else {

         // Decrement by 1 as sqlite indexing is 1-based but interface indexing
         // is 0-based.
         return position - 1;
      }
   }


   private void checkBindRc(T) (int rc, string bindType, int position, T value) {

      switch (rc) {
         case SQLITE_OK:
            break;

         case SQLITE_NOMEM:
            _d_OutOfMemory ();

         case SQLITE_RANGE:
            /* This should not happen in the normal course of events as the
             * position supplied should have been tested against the number of
             * parameters by the caller. There is an assert to ensure that the
             * testing has been done.
             */
            throw SqliteError!(SqlInterfaceException) (
               m_iconn.dbcon,
               "Failed to bind "~bindType~" parameter, database reported that "
               "the position supplied is out of range. Either expected position "
               "range check not done or interface is out of sync with database. "
               "Error in the interface, please lodge an issue.")
               .setSql (m_text)
               .setProperty (SqlBindException.K_BINDPOSITION, position)
               .setProperty (SqlBindException.K_BINDUBOUND, m_numBindParameters)
               .setProperty (SqlBindException.K_BINDVALUE, value);

         case SQLITE_MISUSE:
            throw SqliteError!(SqlOperationalException) (
               m_iconn.dbcon,
               "Failed to bind "~bindType~" parameter, either the statement is "
               "finalized, or the database is in an inconsistent state. If the "
               "statement is finalized, then the interface is out of sync with "
               "the database. If the database is in an inconsistent state, "
               "then this may be an interface error or a problem with SQLite. "
               "If the problem can be replicated, please log an issue with a "
               "test case.")
               .setSql (m_text)
               .setProperty (SqlBindException.K_BINDPOSITION, position)
               .setProperty (SqlBindException.K_BINDUBOUND, m_numBindParameters)
               .setProperty (SqlBindException.K_BINDVALUE, value);

         default:
            throw SqliteError!(SqlOperationalException) (
               m_iconn.dbcon,
               "Failed to bind "~bindType~" parameter. Unexpected error. If you "
               "suspect that the error is with the interface, and you can "
               "replicate the problem in a test case, please log the issue with "
               "the test case.")
               .setSql (m_text)
               .setProperty (SqlBindException.K_BINDPOSITION, position)
               .setProperty (SqlBindException.K_BINDUBOUND, m_numBindParameters)
               .setProperty (SqlBindException.K_BINDVALUE, value);
      }
   }


   void bindBlob (int position, void[] value)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
      assert (position >= 0,
         "Invalid position value (" ~ str (position) ~ "). Must be >= 0.");
      assert (position < m_numBindParameters,
         "Invalid position value (" ~ str (position) ~
         "). Must be < m_numBindParameters (" ~ str (m_numBindParameters) ~ ").");
   }
   body {
      int rc = sqlite3_bind_blob (
         m_stmt, position + 1, value.ptr, value.length,
         SQLITE_TRANSIENT);
      checkBindRc!(typeof(value)) (rc, "blob", position, value);
   }


   void bindDouble (int position, double value)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
      assert (position >= 0,
         "Invalid position value (" ~ str (position) ~ "). Must be >= 0.");
      assert (position < m_numBindParameters,
         "Invalid position value (" ~ str (position) ~
         "). Must be < m_numBindParameters (" ~ str (m_numBindParameters) ~ ").");
   }
   body {
      int rc = sqlite3_bind_double (m_stmt, position + 1, value);
      checkBindRc!(typeof(value)) (rc, "double", position, value);
   }


   void bindInt (int position, int value)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
      assert (position >= 0,
         "Invalid position value (" ~ str (position) ~ "). Must be >= 0.");
      assert (position < m_numBindParameters,
         "Invalid position value (" ~ str (position) ~
         "). Must be < m_numBindParameters (" ~ str (m_numBindParameters) ~ ").");
   }
   body {
      int rc = sqlite3_bind_int (m_stmt, position + 1, value);
      checkBindRc!(typeof(value)) (rc, "int", position, value);
   }


   void bindLong (int position, long value)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
      assert (position >= 0,
         "Invalid position value (" ~ str (position) ~ "). Must be >= 0.");
      assert (position < m_numBindParameters,
         "Invalid position value (" ~ str (position) ~
         "). Must be < m_numBindParameters (" ~ str (m_numBindParameters) ~ ").");
   }
   body {
      int rc = sqlite3_bind_int64 (m_stmt, position + 1, value);
      checkBindRc!(typeof(value)) (rc, "long", position, value);
   }


   void bindNull (int position)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
      assert (position >= 0,
         "Invalid position value (" ~ str (position) ~ "). Must be >= 0.");
      assert (position < m_numBindParameters,
         "Invalid position value (" ~ str (position) ~
         "). Must be < m_numBindParameters (" ~ str (m_numBindParameters) ~ ").");
   }
   body {
      int rc = sqlite3_bind_null (m_stmt, position + 1);
      checkBindRc!(typeof(null)) (rc, "NULL", position, null);
   }


   void bindUtf8 (int position, string value)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
      assert (position >= 0,
         "Invalid position value (" ~ str (position) ~ "). Must be >= 0.");
      assert (position < m_numBindParameters,
         "Invalid position value (" ~ str (position) ~
         "). Must be < m_numBindParameters (" ~ str (m_numBindParameters) ~ ").");
   }
   body {
      int rc = sqlite3_bind_text (
         m_stmt, position + 1, toStringz (value), value.length,
         SQLITE_TRANSIENT);
      checkBindRc!(typeof(value)) (rc, "utf8", position, value);
   }

/+
   Removed until I can determine how to work with UTF16 data in SQLite in the
   interface.

   void bindUtf16 (int position, wchar[] value)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve statement text as Statement instance is "
         "closed.");
      assert (position >= 0,
         "Invalid position value (" ~ str (position) ~ "). Must be >= 0.");
      assert (position < m_numBindParameters,
         "Invalid position value (" ~ str (position) ~
         "). Must be < m_numBindParameters (" ~ str (m_numBindParameters) ~ ").");
   }
   body {
      int rc = sqlite3_bind_text16 (
         m_stmt, position + 1, value.ptr, value.length * 2,
         SQLITE_TRANSIENT);
      checkBindRc!(typeof(value)) (rc, "utf16", position, value);
   }
+/

   //---------------------------------------------------------------------------
   // Section: Compiled Statement Meta Information Methods
   //
   // The following methods query the compiled statement meta information.
   //---------------------------------------------------------------------------

   /**
    * Returns the number of columns expected by the compiled SQL statement.
    */
   int getStmtColCount ()
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");

      assert (m_stmt !is null,
         "Unable to retrieve column count as Statement instance is closed.");
   }
   body {
      return m_compiledColumnCount;
   }



   string getStmtColName (int colnum)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve column name as Statement instance is closed.");
      assert (colnum >= 0,
         "Unable to get column name as colnum (" ~ str (colnum) ~
         ") is a negative number. Only positive column numbers are supported.");
      assert (colnum < m_compiledColumnCount,
         "Unable to get column name as colnum (" ~ str (colnum) ~
         ") > available columns (" ~ str (m_compiledColumnCount) ~ ")");
   }
   body {
      char* name = sqlite3_column_name (m_stmt, colnum);
      return str (name);
   }



   string getStmtColType (int colnum)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve column type as Statement instance is closed.");
      assert (colnum >= 0,
         "Unable to get column type as colnum (" ~ str (colnum) ~
         ") is a negative number. Only positive column numbers are supported.");
      assert (colnum < m_compiledColumnCount,
         "Unable to get column type as colnum (" ~ str (colnum) ~
         ") > available columns (" ~ str (m_compiledColumnCount) ~ ")");
   }
   body {
      char* type = sqlite3_column_decltype (m_stmt, colnum);
      if (null == type) {
         return "EXPRESSION";
      } else {
         return str (type);
      }
   }

   //---------------------------------------------------------------------------
   // Section: Result Set Meta Information Methods
   //
   // The following methods query the result set meta information.
   //---------------------------------------------------------------------------

   /**
    * Returns the number of columns returned by the last step operation.
    */
   int getRsColCount ()
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve resultset column count as Statement instance is "
         "closed.");
   }
   body {
      return m_resultsetColumnCount;
   }



   int getRsColType (int colnum)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve column type as Statement instance is closed.");
      assert (colnum >= 0,
         "Unable to get column type as colnum (" ~ str (colnum) ~
         ") is a negative number. Only positive column numbers are supported.");
      assert (colnum < m_resultsetColumnCount,
         "Unable to get column type as colnum (" ~ str (colnum) ~
         ") > available columns (" ~ str (m_resultsetColumnCount) ~ ")");
   }
   body {
      return sqlite3_column_type (m_stmt, colnum);
   }

   //---------------------------------------------------------------------------
   // Section:
   //---------------------------------------------------------------------------

   Box getValue (int colnum)
   in {
      assert (m_iconn.isOpen (),
         "Operation on cannot be done as connection is closed.");
      assert (m_stmt !is null,
         "Unable to retrieve column value as Statement instance is closed.");
      assert (colnum >= 0,
         "Unable to get column value as colnum (" ~ str (colnum) ~
         ") is a negative number. Only positive column numbers are supported.");
      assert (colnum < m_resultsetColumnCount,
         "Unable to get column value as colnum (" ~ str (colnum) ~
         ") > available columns (" ~ str (m_resultsetColumnCount) ~ ")");
   }
   body {
      Box value;

      int type = sqlite3_column_type (m_stmt, colnum);

      switch (type) {
         case SQLITE_INTEGER:
            // The SQLite interface does not tell us the integer type but it
            // can be infered by always retrieving a 64bit int and trying
            // to fit it into an int.

            sqlite_int64 v = sqlite3_column_int64 (m_stmt, colnum);

            if (v > int.max) {
               value = box (v);
            } else {
               value = box (cast(int)(v));
            }
            break;

         case SQLITE_FLOAT:
            double v = sqlite3_column_double (m_stmt, colnum);
            value = box (v);
            break;


         case SQLITE_TEXT:
            char* v = sqlite3_column_text (m_stmt, colnum);
            /* The .dup is necessary as toString(char*) does not copy the
             * contents of the char*, it just wraps it. Without the clone,
             * every text box returned by this function would display as
             * the last string constructed.
             */
            value = box (str (v).dup);
            break;


         case SQLITE_BLOB:
            void* v = sqlite3_column_blob (m_stmt, colnum);
            int size = sqlite3_column_bytes (m_stmt, colnum);
            value = box (v [0 .. size].dup);
            break;

         case SQLITE_NULL:
            value = box (null);
            break;

         default:
            throw (new SqlInterfaceException (
               "Unable to retrieve value for column (" ~ str (colnum) ~ "). "
               "Do not know how to handle unexpected type code (" ~ str (type) ~
               ")."))
               .setSql (m_text);

      }

      return value;
   }

   //---------------------------------------------------------------------------
   // Section:
   //---------------------------------------------------------------------------

   version (sqlite_enable_column_metadata) {
      string getColumnDatabaseName (int colnum) {
         char* name = sqlite3_column_database_name (m_stmt, colnum);
         if (null == name) {
            return "";
         } else {
            return str (name);
         }
      }

      string getColumnTableName (int colnum) {
         char* name = sqlite3_column_table_name (m_stmt, colnum);
         if (null == name) {
            return "";
         } else {
            return str (name);
         }
      }
   }


   //---------------------------------------------------------------------------
   // Section:
   //---------------------------------------------------------------------------


   /**
    * Parse the provided statement text and determine the type.
    *
    * The statement may be one of the following:
    *
    *   STATEMENT_QUERY       - The select statement is a query statement.
    *   STATEMENT_MODIFY      - The insert, update, replace, and delete
    *                           statements are statements that modify data.
    *   STATEMENT_TRANSACTION - The begin, commit, end and rollback statement
    *                           are transaction statements.
    *   STATEMENT_OTHER       - All other statements such as create, drop,
    *                           pragma, etc.
    *
    * To determine the statement type, leading whitespace is ignored and the
    * first word is read and mapped to the statement type. Leading comments will
    * cause an error to be thrown. An empty statement will cause an invalid
    * statement exception to be thrown.
    */
   private StatementType determineStatementType (string text) {

      StatementType st;

      /* Strip is non-destructive so text does not need to be cloned.
       */
      string operation = strip (text);

      if (operation.length == 0) {
         throw new SqlProgrammingException (
            "Invalid SQL statement, expecting an operation but received " ~
            (text.length == 0
               ? "an empty string." :
                 "a string consisting of only white spaces."));
      }

      /* Find the end of the first word.
       */
      int i = 0;
      while (i < operation.length && !iswhite (operation[i])) {
         i++;
      }

      /* Splice out the first word, uppercase and test for type.
       *
       * The spliced string is not cloned because the 'toupper' function is
       * implemented as copy-on-write thus will clone for us.
       */
      operation = toupper (operation[0 .. i]);

      if (operation == "SELECT") {
         st = StatementType.STATEMENT_QUERY;

      } else if (inAr (operation, ["INSERT", "UPDATE", "REPLACE", "DELETE"])) {
         st = StatementType.STATEMENT_MODIFY;

      } else if (inAr (operation, ["BEGIN", "END", "COMMIT", "ROLLBACK"])) {
         st = StatementType.STATEMENT_TRANSACTION;

      } else {
         st = StatementType.STATEMENT_OTHER;
      }

      return st;
   }

   unittest {

      Statement st = new Statement ();

      void assertType (string text, StatementType type) {
         assert (st.determineStatementType (text) == type);
         assert (st.determineStatementType ("  " ~ text) == type);
         assert (st.determineStatementType (text ~ "  ") == type);
         assert (st.determineStatementType ("  " ~ text ~ "  ") == type);

         assert (st.determineStatementType (toupper(text)) == type);
         assert (st.determineStatementType ("  " ~ toupper(text)) == type);
         assert (st.determineStatementType (toupper(text) ~ "  ") == type);
         assert (st.determineStatementType ("  " ~ toupper(text) ~ "  ") == type);
      }

      void assertQry (string text) {
         assertType (text, StatementType.STATEMENT_QUERY);
      }

      void assertMod (string text) {
         assertType (text, StatementType.STATEMENT_MODIFY);
      }

      void assertXac (string text) {
         assertType (text, StatementType.STATEMENT_TRANSACTION);
      }

      void assertOth (string text) {
         assertType (text, StatementType.STATEMENT_OTHER);
      }

      assertQry ("select * from tbl");
      assertMod ("insert into tbl values (1)");
      assertMod ("insert or replace into tbl values (1)");
      assertMod ("insert or rollback into tbl values (1)");
      assertMod ("insert or abort into tbl values (1)");
      assertMod ("insert or fail into tbl values (1)");
      assertMod ("insert or ignore into tbl values (1)");
      assertMod ("update tbl set x = 1");
      assertMod ("update or replace tbl set x = 1");
      assertMod ("update or rollback tbl set x = 1");
      assertMod ("update or abort tbl set x = 1");
      assertMod ("update or fail tbl set x = 1");
      assertMod ("update or ignore tbl set x = 1");
      assertMod ("replace into tbl values (1)");
      assertMod ("delete from tbl where x = 1");
      assertXac ("begin transaction");
      assertXac ("begin deferred");
      assertXac ("begin immediate");
      assertXac ("begin exclusive");
      assertXac ("commit");
      assertXac ("rollback");
      assertXac ("end");
      assertOth ("pragma auto_vacuum");
      assertOth ("reindex collationname");
      assertOth ("create table tbl (a varchar2 not null)");
      assertOth ("analyze");
      assertOth ("analyze database-name");
      assertOth ("analyze database-name.table-name");
      assertOth ("vacuum");
      assertOth ("vacuum index-or-table-name");
   }
}



private bool inAr(T) (T val, T[] ar) {
   foreach (T x ; ar) {
      if (val == x) return true;
   }
   return false;
}
